package temp;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Locale;

/**
 * User: Vladimir
 * Date: 23.11.12 Time: 10:44
 */
public class ExcelFormula {
    public static final String XLS_PATH = "data/extramobi/test/";

    public static void main(String[] args) throws IOException, WriteException {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream(XLS_PATH + "workbook.xls");
        HSSFSheet sheet = wb.createSheet();
        HSSFRow row = sheet.createRow(0);
//        HSSFCell cell = row.createCell(0);
//        cell.setCellValue(1);
        row.createCell(0).setCellValue("cell 1 , 1");
        row.createCell(1).setCellValue("cell 1 , 2");
        row.createCell(2).setCellValue("cell 1 , 3");
        row = sheet.createRow(1);
        row.createCell(0).setCellFormula("A1&\" and \"&A1");
        row.createCell(1).setCellFormula("B1&B1");
//        row.createCell(2).setCellFormula(String.format("LOWER(\"Phone_\"&SUBSTITUTE(A%s;\" \";\"_\")&\"_\"&SUBSTITUTE(B%s;\" \";\"_\"))", 1, 1));
        row.createCell(2).setCellFormula(String.format("SUBSTITUTE(A%s,\" \",\"_\")", 1));
        wb.write(fileOut);
        fileOut.close();
/*
        WorkbookSettings s = new WorkbookSettings();
        s.setLocale(new Locale("ru", "RU"));
        File xlsFile = new File(XLS_PATH + "phone.test.xls");
        WritableWorkbook workbook = Workbook.createWorkbook(xlsFile, s);
        WritableSheet sheet = workbook.createSheet("List 1", 0);
        sheet.addCell(new Label(0,0,"label1"));
        sheet.addCell(new Label(1,0,"label2"));
        sheet.addCell(new Label(2,0,"label3"));
        sheet.addCell(new Formula(0,1,"A1"));
        sheet.addCell(new Formula(1,1,"B1"));
        sheet.addCell(new Formula(2,1,"C1"));
        sheet.addCell(new Formula(0,2,"A2"));
        sheet.addCell(new Formula(1,2,"B2"));
        sheet.addCell(new Formula(2,2,"C2"));
        sheet.addCell(new Formula(0,3,"сцепить(A1;A2)"));
//        sheet.addCell(new Formula(1,3,"B1&B2"));
//        sheet.addCell(new Formula(2,3,"C1&C2"));
        workbook.write();
        workbook.close();
*/
    }
}
